﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Common;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CtrlBDProject
/// </summary>
public class CtrlBDProject
{
    String strConnect;
    String strSql;
    SqlConnection connection;
    SqlDataAdapter adapter;
    SqlCommand command;
    DataSet dataSet;

    /// <summary>
    /// Constructor vacío.
    /// </summary>
	public CtrlBDProject()
	{
        strConnect = ConfigurationManager.ConnectionStrings["conexion"].ConnectionString;
        connection = new SqlConnection(strConnect);
	}

    /// <summary>
    /// Devuelve un dataset con la consulta hecha, solamente Id y Nombre de todos los proyectos.
    /// </summary>
    public DataSet getData()
    {
        dataSet = new DataSet();

        connection.Open();
        strSql = "SELECT Id, Name FROM Project" + extra;
        adapter = new SqlDataAdapter(strSql, connection);
        adapter.Fill(dataSet, "Project");
        connection.Close();
        return dataSet;
    }

    public DataSet getDataC(int iId)
    {
        dataSet = new DataSet();
        connection.Open();
        strSql = "EXEC consultProjectC @id";
        command = new SqlCommand(strSql, connection);
        SqlParameter p1 = new SqlParameter("@id", iId);
        command.Parameters.Add(p1);
        adapter = new SqlDataAdapter(command);
        adapter.Fill(dataSet, "Project");
        connection.Close();
        return dataSet;
    }

    /// <summary>
    /// Devuelve un encapsulado del proyecto que se mandó a consultar.
    /// </summary>
    /// <param name="_iIdProject">Identificador del proyecto a consultar.</param>
    public Project getProject(int iId)
    {
        DataTable table = new DataTable();
        connection.Open();

        command = new SqlCommand("EXEC consultProject @id", connection);
        SqlParameter p1 = new SqlParameter("@id", iId);
        command.Parameters.Add(p1);
        adapter = new SqlDataAdapter(command);
        adapter.Fill(table);
        System.IFormatProvider format = new System.Globalization.CultureInfo("es-ES", true);
        /*string bla = ((table.Rows[0][3]).ToString());
        DateTime blo = DateTime.Parse(bla);
        bla = ((table.Rows[0][4]).ToString());
        blo = DateTime.Parse(bla);*/
        Project project = new Project(Int32.Parse(table.Rows[0][0].ToString()), table.Rows[0][1].ToString(), table.Rows[0][2].ToString(), DateTime.Parse(((table.Rows[0][3]).ToString())), DateTime.Parse(((table.Rows[0][4]).ToString())), float.Parse(table.Rows[0][5].ToString()), table.Rows[0][6].ToString());
        connection.Close();
        return project;
    }
    
    
    /// <summary>
    /// Recibe un encapsulado de proyecto para agregarlo en la base de datos.
    /// </summary>
    /// <param name="project">Encapsulado de proyecto sin ID para agregar en la base de datos.</param>
    public void addProject(Project project)
    {
        connection.Open();
        strSql = "EXEC addProject @name, @description, @start, @end, @duration, @objectives";

        command = new SqlCommand(strSql, connection);
        SqlParameter p1 = new SqlParameter("@name", project.sName);
        command.Parameters.Add(p1);

        SqlParameter p2 = new SqlParameter("@description", project.sDescription);
        command.Parameters.Add(p2);

        SqlParameter p3 = new SqlParameter("@start", SqlDbType.DateTime);
        p3.Value = project.dStartingDate;
        command.Parameters.Add(p3);

        SqlParameter p4 = new SqlParameter("@end", SqlDbType.DateTime);
        p4.Value = project.dCompletionDate;
        command.Parameters.Add(p4);

        SqlParameter p5 = new SqlParameter("@duration", project.fEstimatedDuration);
        command.Parameters.Add(p5);

        SqlParameter p6 = new SqlParameter("@objectives", project.sObjectives);
        command.Parameters.Add(p6);

        command.ExecuteNonQuery();
        connection.Close();
    }

    /// <summary>
    /// Recibe un encapsulado completo de Proyecto para modificarlo en la base de datos.
    /// </summary>
    /// <param name="project">Encapsulado de Proyecto.</param>
    public void setProject(Project project)
    {
        connection.Open();
        strSql = "EXEC modifyProject @id, @name, @description, @start, @end, @duration, @objectives";
        command = new SqlCommand(strSql, connection);

        SqlParameter p0 = new SqlParameter("@id", project.iId);
        command.Parameters.Add(p0);

        SqlParameter p1 = new SqlParameter("@name", project.sName);
        command.Parameters.Add(p1);

        SqlParameter p2 = new SqlParameter("@description", project.sDescription);
        command.Parameters.Add(p2);

        SqlParameter p3 = new SqlParameter("@start", SqlDbType.DateTime);
        p3.Value = project.dStartingDate;
        command.Parameters.Add(p3);

        SqlParameter p4 = new SqlParameter("@end", SqlDbType.DateTime);
        p4.Value = project.dCompletionDate;
        command.Parameters.Add(p4);

        SqlParameter p5 = new SqlParameter("@duration", project.fEstimatedDuration);
        command.Parameters.Add(p5);

        SqlParameter p6 = new SqlParameter("@objectives", project.sObjectives);
        command.Parameters.Add(p6);

        command.ExecuteNonQuery();
        connection.Close();
    }

    /// <summary>
    /// Recibe un entero Id para eliminar de la base de datos.
    /// </summary>
    String extra = " where name != 'zzzzzzz'";
    /// <param name="_iIdProject">Identificador del proyecto que se desea eliminar.</param>
    public void deleteProject(int iId)
    {
        connection.Open();
        strSql = "EXEC deleteProject @id";
        command = new SqlCommand(strSql, connection);

        SqlParameter p1 = new SqlParameter("@id", iId);
        command.Parameters.Add(p1);

        command.ExecuteNonQuery();
        connection.Close();
    }
}
